/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.core.dao.QualificationDAO;
import com.core.database.Manager;
import com.core.entities.Qualification;
import com.core.exception.DAOException;

/**
 * 
 * @author himanasser
 */
public class QualificationDAOImpl implements QualificationDAO {
		Qualification qualification = new Qualification();
		ArrayList<Qualification> qualificationsList = new ArrayList<Qualification>();


	Connection connection = null;
	private static QualificationDAOImpl instance=null;
	public static QualificationDAOImpl getInstance(){
		if(instance==null){
			instance=new QualificationDAOImpl();
		}
		return instance;
	}

	@Override
	public Qualification findByName(String name) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM qualification WHERE name = '" + name
					+ "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					qualification.setId(rs.getInt(1));
					qualification.setName(rs.getString(2));
					qualification.setCollage(rs.getString(3));
					qualification.setDegree(rs.getString(4));
					qualification.setSpecificationId(rs.getInt(5));
					qualification.setYear(rs.getString(6));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return qualification;

	}

	@Override
	public Qualification findByCollage(String collage) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM qualification WHERE collage = '"
					+ collage + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					qualification.setId(rs.getInt(1));
					qualification.setName(rs.getString(2));
					qualification.setCollage(rs.getString(3));
					qualification.setDegree(rs.getString(4));
					qualification.setSpecificationId(rs.getInt(5));
					qualification.setYear(rs.getString(6));
				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return qualification;
	}

	@Override
	public Qualification findByDegree(String degree) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM qualification WHERE degree = '"
					+ degree + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					qualification.setId(rs.getInt(1));
					qualification.setName(rs.getString(2));
					qualification.setCollage(rs.getString(3));
					qualification.setDegree(rs.getString(4));
					qualification.setSpecificationId(rs.getInt(5));
					qualification.setYear(rs.getString(6));

					System.out.println(qualification);
				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return qualification;
	}

	@Override
	public List<Qualification> findBySpecificationId(int specification_id)
			throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM qualification WHERE specification_id = '"
					+ specification_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					qualification.setId(rs.getInt(1));
					qualification.setName(rs.getString(2));
					qualification.setCollage(rs.getString(3));
					qualification.setDegree(rs.getString(4));
					qualification.setSpecificationId(rs.getInt(5));
					qualification.setYear(rs.getString(6));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return qualificationsList;
	}

	@Override
	public Qualification findByYear(String year) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM qualification WHERE year = '" + year
					+ "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					qualification.setId(rs.getInt(1));
					qualification.setName(rs.getString(2));
					qualification.setCollage(rs.getString(3));
					qualification.setDegree(rs.getString(4));
					qualification.setSpecificationId(rs.getInt(5));
					qualification.setYear(rs.getString(6));
				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return qualification;
	}

	@Override
	public void insert(Qualification o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " INSERT INTO qualification (name, collage, degree, specification_id, year) "
					+ "VALUE (?, ?, ?, ?, ? )";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, o.getName());
			ps.setString(2, o.getCollage());
			ps.setString(3, o.getDegree());
			ps.setInt(4, o.getSpecificationId());
			ps.setString(5, o.getYear());

			int result = ps.executeUpdate();
			if (result != 1) {
				throw new SQLException("no row inserted!");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new RuntimeException("Cannot add new Qualification", ex);
		} finally {
			Manager.closeConnection(connection);
		}
	}

	@Override
	public Qualification findById(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT *FROM qualification WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					qualification.setId(rs.getInt(1));
					qualification.setName(rs.getString(2));
					qualification.setCollage(rs.getString(3));
					qualification.setDegree(rs.getString(4));
					qualification.setSpecificationId(rs.getInt(5));
					qualification.setYear(rs.getString(6));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return qualification;
	}

	@Override
	public List<Qualification> findAll() throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " SELECT * FROM qualification ";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				qualification.setId(rs.getInt(1));
				qualification.setName(rs.getString(2));
				qualification.setCollage(rs.getString(3));
				qualification.setDegree(rs.getString(4));
				qualification.setSpecificationId(rs.getInt(5));
				qualification.setYear(rs.getString(6));

				qualificationsList.add(qualification);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return qualificationsList;
	}

	@Override
	public void delete(int id) throws DAOException {
		Qualification qualification = new Qualification();
		try {
			connection = Manager.getConnection();
			String sql = " DELETE FROM qualification WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate();
			if (result != 1) {
				ps.setString(1, qualification.getName());
				ps.setString(2, qualification.getCollage());
				ps.setString(3, qualification.getDegree());
				ps.setInt(4, qualification.getSpecificationId());
				ps.setString(5, qualification.getYear());
			}

		} catch (SQLException s) {
			s.printStackTrace();

		}
	}

	@Override
	public void update(Qualification o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "UPDATE qualification SET name = '" + o.getName()
					+ " ', collage = '" + o.getCollage() + " ', degree = '"
					+ o.getDegree() + " ', specification_id = '"
					+ o.getSpecificationId() + " ', year = '" + o.getYear()
					+ "' WHERE id = " + o.getId();
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate(sql);

			if (result != 1) {
				ps.setString(1, o.getName());
				ps.setString(2, o.getCollage());
				ps.setString(3, o.getDegree());
				ps.setInt(4, o.getSpecificationId());
				ps.setString(5, o.getYear());
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
	}
}
